SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.vwlLicenciasSinConfirmar
AS
SELECT     TOP 100 PERCENT SSA.idSubServicio AS ID, Ag.Documento, Ag.Nombres AS ApellidoyNombre, Org.CUISE, SSA.FechaAlta AS AltaLic, 
                      SSA.FechaBaja AS BajaLic, SSA.Licencia AS CodLic, Lic.Descripcion AS Licencia, Enf.CodigoAlfanumerico AS Enfermedad, SSA.confirmada AS LConf, 
                      YEAR(SSA.FechaAlta) AS Año
FROM         dbo.tb_SubserviciosAgentes SSA INNER JOIN
                      dbo.tb_ServiciosAgentes SA ON SA.idServicioAgente = SSA.ServicioAgente INNER JOIN
                      dbo.tb_Licencias Lic ON Lic.idLicencia = SSA.Licencia INNER JOIN
                      dbo.tb_Organizaciones Org ON Org.idOrganizaciones = SA.UbicacionOrig INNER JOIN
                      dbo.tb_Agentes Ag ON Ag.idAgente = SA.Agente LEFT OUTER JOIN
                      dbo.tb_Enfermedades Enf ON Enf.idEnfermedad = SSA.CodEnfermedad
WHERE     (SSA.Licencia IS NOT NULL) AND (YEAR(SSA.FechaAlta) >= 2007) AND (SSA.confirmada = 'N') OR
                      (SSA.Licencia IS NOT NULL) AND (YEAR(SSA.FechaAlta) >= 2007) AND (SSA.CodEnfermedad IS NULL) AND (Lic.RequiereRegEnfermedad = 'S')
GO
GRANT SELECT ON  [dbo].[vwlLicenciasSinConfirmar] TO [Informatica]
GRANT SELECT ON  [dbo].[vwlLicenciasSinConfirmar] TO [PersonalDocente]
GRANT SELECT ON  [dbo].[vwlLicenciasSinConfirmar] TO [PersonalDocenteDireccion]
GRANT SELECT ON  [dbo].[vwlLicenciasSinConfirmar] TO [SoloVer]
GO
EXEC sp_addextendedproperty N'MS_DiagramPane1', N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[40] 4[20] 2[20] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1[50] 2[25] 3) )"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1 [56] 4 [18] 2))"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "SSA"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 114
               Right = 229
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "SA"
            Begin Extent = 
               Top = 6
               Left = 267
               Bottom = 114
               Right = 493
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "Lic"
            Begin Extent = 
               Top = 6
               Left = 531
               Bottom = 114
               Right = 732
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "Org"
            Begin Extent = 
               Top = 6
               Left = 770
               Bottom = 114
               Right = 959
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "Ag"
            Begin Extent = 
               Top = 6
               Left = 997
               Bottom = 114
               Right = 1198
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "Enf"
            Begin Extent = 
               Top = 114
               Left = 38
               Bottom = 222
               Right = 280
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      RowHeights = 220
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 1440
         Alias = 900', 'USER', N'dbo', 'VIEW', N'vwlLicenciasSinConfirmar', NULL, NULL
GO
EXEC sp_addextendedproperty N'MS_DiagramPane2', N'
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
', 'USER', N'dbo', 'VIEW', N'vwlLicenciasSinConfirmar', NULL, NULL
GO
DECLARE @xp int
SELECT @xp=2
EXEC sp_addextendedproperty N'MS_DiagramPaneCount', @xp, 'USER', N'dbo', 'VIEW', N'vwlLicenciasSinConfirmar', NULL, NULL
GO
